﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Spreadsheet;
using SpreadsheetLight;
using UtilsLib.UtilsFile.CSV;
using UtilsLib.UtilsLog;
using UtilsLib.UtilsFile.XLSX;

namespace UtilsLib.UtilsFile
{
    public static class ReadFromFile
    {
        public static FileObject ReadCSV(string filename, char separator = ';')
        {
            FileObject fo = new FileObject();
            CsvFileReader.separator = separator;
            try
            {
                using (CsvFileReader reader = new CsvFileReader(filename))
                {
                    CsvRow row = new CsvRow();
                    while (reader.ReadRow(row))
                    {
                        FileRow frow = new FileRow();
                        foreach (string s in row)
                            frow.Columns.Add(s);
                        fo.AddRow(frow.Columns); // ?not sure about that..?
                    }
                }
            }
            catch (Exception ex)
            {
                LogToFile.WriteException(ex, "Error Reading CSV.");
            }
            return fo;
        }

        public static FileObject ReadXLSX(string filename)
        {
            FileObject objFile = new FileObject();

            try
            {
                using (SLDocument objSLD = new SLDocument(filename))
                {
                    SLWorksheetStatistics stats = objSLD.GetWorksheetStatistics();
                    int columnStartIndex = stats.StartColumnIndex;
                    int columnEndIndex = stats.EndColumnIndex;

                    int rowStartIndex = stats.StartRowIndex;
                    int rowEndIndex = stats.EndRowIndex;

                    if (stats.EndColumnIndex > 100) //Issue on 32bit applications -> 16365 columns
                        columnEndIndex = 100;
                    if (stats.EndRowIndex > 500)
                        rowEndIndex = 500;
                    /// Notice that the StartRowIndex returned isn't necessarily the
                    /// first row of the worksheet, it's the first row that has any data.

                    /// WARNING: the statistics object notes down any non-empty cells.
                    /// This includes cells with say a background colour but doesn't have
                    /// cell data. So if you have an empty row just after your worksheet
                    /// tabular data, but the row is coloured light blue, the EndRowIndex
                    /// will be one more than you need.
                    /// It is suggested that you know more about the input Excel file you're
                    /// using...

                    /// I'm also not using any variables to store the intermediate returned
                    /// cell data. This makes each code segment independent of each other,
                    /// and also makes it such that it's easier for you to see what you
                    /// actually have to type.

                    objSLD.UnmergeWorksheetCells(0, 0, rowEndIndex, columnEndIndex);

                    for (int rowIndex = rowStartIndex; rowIndex <= rowEndIndex; ++rowIndex)
                    {
                        List<FileColumn> columnValues = new List<FileColumn>();
                        for (int columnIndex = columnStartIndex; columnIndex <= columnEndIndex; columnIndex++)
                        {
                            SLRstType rst = objSLD.GetCellValueAsRstType(rowIndex, columnIndex);
                            string test = objSLD.GetCellValueAsString(rowIndex, columnIndex);
                            String retour = GetFormattedText(rst);
                            columnValues.Add(retour);
                        }
                        objFile.AddRow(columnValues);
                    }
                    objSLD.CloseWithoutSaving();                    
                }
            }
            catch (Exception ex)
            {
                LogToFile.WriteException(ex, "Error Reading XSLX.");
            }
            finally
            {
                LogToFile.WriteMessage("XLSX parsing End.");
            }
            return objFile;
        }

        /// <summary>
        /// Retourne du texte mis en forme selon le style appliqué
        /// </summary>
        /// <param name="rst">Rich String Type</param>
        /// <returns></returns>
        private static string GetFormattedText(SLRstType rst)
        {
            //Si aucune mise en forme définie
            if (rst.GetRuns().Count() <= 0)
                return rst.ToPlainString();

            String retour = "";
            List<SLRun> lst = rst.GetRuns();
            foreach (SLRun run in lst)
            {
                SLFont font = run.Font;
                bool bold = font.Bold ?? false,
                    italic = font.Italic ?? false,
                    strike = font.Strike ?? false,
                    underline = font.Underline == UnderlineValues.Single;

                string line = "";
                string text = run.Text;
                text = text.Replace("\"", "&quot;");

                while (text.IndexOf("\n") != -1)
                {
                    line += "<br />";
                    text = text.Remove(text.IndexOf("\n"), 1);
                }

                if (bold)
                    text = "<strong>" + text + "</strong>";
                if (italic)
                    text = "<em>" + text + "</em>";
                if (strike)
                    text = "<strike>" + text + "</strike>";
                if (underline)
                    text = "<u>" + text + "</u>";

                if (font.FontFamily != null && font.FontFamily != 2)
                    text = "<span style=\"font-family:" + font.FontName + ";\">" + text + "</span>";

                switch (font.VerticalAlignment)
                {
                    //case VerticalAlignmentRunValues.Baseline:
                    //vAlign = "";//+= "baseline;"; //valeur par défaut
                    //break;
                    case VerticalAlignmentRunValues.Subscript:
                        text = "<sub>" + text + "</sub>";
                        break;
                    case VerticalAlignmentRunValues.Superscript:
                        text = "<sup>" + text + "</sup>";
                        break;
                }
                text += line;

                //string weight = font.Bold ?? false ? "font-weight:Bold;" : "";
                //string italic = font.Italic ?? false ? "font-style:Italic;" : "";
                //string color = "#" + font.FontColor.Name;
                //string outline = font.Outline ?? false ? "text-outline: 2px 2px " + color + ";" : "";
                //string family = "<span style=\"font-family:" + font.FontName + ";\">";
                //string size = font.FontSize != null ? "font-size:" + int.Parse(font.FontSize.ToString()) + "px;" : "";
                //color = "color:" + color + ";";
                //string decoration = "text-decoration:";
                //switch (font.Underline)
                //{
                //    case UnderlineValues.Double:
                //        decoration += "underline overline";
                //        break;
                //    case UnderlineValues.DoubleAccounting:
                //        decoration += "underline overline";
                //        break;
                //    case UnderlineValues.None:
                //        decoration += "none";
                //        break;
                //    case UnderlineValues.Single:
                //        decoration += "underline";
                //        break;
                //    case UnderlineValues.SingleAccounting:
                //        decoration += "overline";
                //        break;
                //}
                //if (font.Strike ?? false)
                //    decoration += " line-through";
                //decoration += ";";

                retour += text;

            }
            return retour;
        }
    }
}
